
alter proc dbo.sp_$dbfiles @dbname sysname = null , @drive char(1) = null, @filegroup varchar(25) = null
	, @full int = 100
as
-- je 2005, 2007
set nocount on set transaction isolation level read uncommitted
set arithabort off set deadlock_priority low set xact_abort off

declare @dbid smallint , @name sysname
declare	@ws int, @w int, @w2 int, @w3 int, @w4 int
declare @cmd nvarchar(4000)
declare @like sysname, @likefg sysname
set @like = case when @dbname is null then '%' when db_id(@dbname) is not null then @dbname else '%' end
set @drive = case when @drive is null then '%' when (@drive >= 'C' and @drive <= 'Z') then @drive else '%' end
set @likefg = case when @filegroup is null then '%' else @filegroup end
if isnumeric(@full) != 1 
	set @full = 100

if object_id('tempdb..#files','U') is not null drop table #files
create table #files(
	dbid		int,
	[dbname]	sysname,
	fileid		smallint,
	groupid 	smallint,
	name		sysname,
	filename	nvarchar(753)
	, [size mb] float
	, [used mb] float
	, [maxsize mb] float
	, [growth] varchar (11) null
--	, is_percent_growth int null
	, groupname sysname null default null
	, [% free] int null default null
--	, [full] varchar(4) null default (null)
, unique clustered (dbname,fileid)
)

set @cmd = N'if 1=HAS_DBACCESS(''?'') and databasepropertyex(''?'',''Status'')=''ONLINE'' and ''?'' = any(select name from master..sysdatabases where status&1048!=1048) begin use ' + quotename( + '?' ) + ' if db_name() like ''' + @like 
+ ''' insert into #files select db_id(), db_name(), f.fileid, f.groupid, rtrim(f.name), rtrim(f.filename)'
+ ' , df.size as [size mb], FILEPROPERTY(f.name, ''spaceused'') [used mb], df.max_size [maxsize mb]'
+ ', case when df.is_percent_growth = 0 then cast(df.growth*8/1024 as varchar(11))+''mb'' else ''10%'' end as [growth]'
-- , cast(df.growth*8/1024 as varchar(11))+case when f.status&0x100000>0 then ''%'' else '''' end [growth]
+ ', isnull(filegroup_name(f.groupid),''log'') [groupname]
, cast(100*(df.size/128-(FILEPROPERTY(f.name,''spaceused'')/128))/(df.size/128.0) as int) [% free]
-- , case when maxsize/128 > 0 then case when FILEPROPERTY(name,''spaceused'')/128 = maxsize/128 or (FILEPROPERTY(name,''spaceused'')/128 + growth/128 ) >= maxsize/128 then ''YES'' else '''' end else '''' end as [full] 
from dbo.sysfiles f 
	inner join sys.database_files df on f.name = df.name and f.fileid = df.file_id
where left(f.filename,1) like ''' + @drive + ''' and isnull(filegroup_name(f.groupid),''log'') like ''' +@likefg + ''' end'

exec sp_MSforeachdb @command1 = @cmd

-- select * from #files
set @ws = 2 + len(@@servername)
select @w = 2 + max(len(dbname))
, @w2 = 2 + max(len(name))
, @w3 = 2 + max(len([groupname]))
, @w4 = 1 + max(len([filename])) from #files

set @cmd = N'select left(convert(varchar,serverproperty(''servername'')),' + convert(varchar,@ws) + ') [instance]
, d.dbid as dbid
, left([dbname],' + convert(varchar,@w) + ') as dbname
, left(#f.name,' + convert(varchar,@w2) + ') [name]
, [% free]
, ceiling(cast([size mb]/128 as float)) as [allocated mb]
, ceiling(cast([used mb]/128 as float)) as [used mb]
-- , cast(mf.size*1.0/128 as bigint) as [d.mb]
, cast(case when [maxsize mb] = -1 then -1 else (([maxsize mb]*8.0)/1024) end as int) [maxsize mb]
, #f.[growth] as [growth]
, substring(#f.[filename],1,' + convert(varchar,@w4) + ') as [filename]
, fileid
, convert(varchar(12),(case mf.type_desc when ''ROWS'' then ''data only'' when ''LOG'' then ''log'' end)) as [type_desc]	--(case sysfiles.status & 0x40 when 0x40 then ''log only'' else ''data only'' end)) as type_desc
, left([groupname],' + convert(varchar,@w3) + ') as [filegroupName]
-- , d.size as [allocated mb]
--, cast(d.recovery_model_desc as varchar(12)) as [recovery_model]	--, cast(databasepropertyex(d.name,''recovery'') as char(12)) as [recovery]
-- , convert(varchar,getdate(),120) as timeof
-- into msdb..dbfiles__
from 
	#files #f 
	inner join master..sysdatabases d on #f.dbid = d.dbid 
	join sys.master_files mf on mf.database_id = #f.dbid and #f.fileid = mf.file_id
		and #f.dbname like ''' + @like + ''' 
		and [% free] <= isnull(' + convert(varchar,@full) + ',100)
order by 
	#f.dbid , #f.fileid
	--, mf.type_desc desc
	, case #f.groupname when ''primary'' then 0 when ''log'' then 1 else ascii(left(#f.groupname,1)) end '

exec (@cmd)

-- select * from #files

if object_id('tempdb..#files','U') is not null drop table #files

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

exec sp_MS_marksystemobject sp_$dbfiles
go

exec sp_$dbfiles


USE [master]
GO

if object_id('dbo.sp_dbfiles','p') is not null
	drop proc dbo.sp_dbfiles
go

/****** Object:  StoredProcedure [dbo].[sp_dbfiles]    Script Date: 03/07/2009 10:27:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_dbfiles] @db sysname = NULL
as
-- JE 2008
set nocount on set implicit_transactions off set xact_abort on
declare @s nvarchar(4000) 
set @s = case when db_id(@db) is null then '-- ' 
else case when 1 = HAS_DBACCESS(@db) and databasepropertyex(@db,'Status')='ONLINE' then 'use ['+@db+']' end end + char(10)
+ ' select serverproperty(''servername'') as [instance]
, db_id() as dbid
, db_name() as dbname, f.name, f.physical_name
, f.file_id
, convert(varchar(12),(case f.type_desc when ''ROWS'' then ''data only'' when ''LOG'' then ''log'' end)) as [type_desc]	--(case sysfiles.status & 0x40 when 0x40 then ''log only'' else ''data only'' end)) as type_desc
, fg.name as filegroupname
, size/128 as [allocated mb]
, cast(FILEPROPERTY(f.name,''spaceused'')/128.0 as numeric(14,1)) as [used mb]
, floor(cast(100*(size/128-(FILEPROPERTY(f.name,''spaceused'')/128))/(size/128.0) as int)) [% free]
, case when f.is_percent_growth = 0 then cast(f.growth*8/1024 as varchar(11))+''mb'' else ''10%'' end as [growth]
, f.is_percent_growth
, case when f.max_size in (0,-1) then f.max_size else f.max_size/128 end as max_size_mb
, f.data_space_id
-- , fg.type, fg.type_desc
, fg.is_default 
, f.is_read_only
, cast(getdate() as varchar) as timeof
from sys.database_files f 
left outer join sys.filegroups fg on f.data_space_id = fg.data_space_id order by file_id'
-- print @s
exec (@s)

go

